from django.shortcuts import render
from django.db.models import *
from typing import List, Union
from django.db import connection
from django.db import connections
from datetime import datetime
from .models import Down, Repair, Parse, Down1, Parse1, DownParse, ParseDown
# 只允许post请求进入视图函数
from django.views.decorators.http import require_http_methods


# 起始页面
def parse1(request):
    start_time, stop_time = initial_time()

    table, pic_name, cmy, crew_length = zfh(start_time, stop_time)

    time = [start_time, stop_time]
    context = {'time': time, 'table': table, 'pic_name': pic_name, 'cmy': cmy, 'crew_length': crew_length}

    return render(request, 'production_ms/parse/template_parse.html', context)


# 接收date后返回新的图表
@require_http_methods(["POST"])
def parse2(request):
    start_time = request.POST.get('start')
    stop_time = request.POST.get('stop')
    table, pic_name, cmy, crew_length = zfh(start_time, stop_time)
    context = {'table': table, 'pic_name': pic_name, 'cmy': cmy, 'crew_length': crew_length}

    return render(request, 'production_ms/parse/parse.html', context)


def zfh(start_time, stop_time):
    with connections['production'].cursor() as cur:
    
        # date范围限制
        hxy_r = f'''
            date >= '{start_time}' and date <= '{stop_time}'
        '''
    
        cur.execute(f'''
                select distinct unit from parse_down where {hxy_r}
        ''')
        crew = list(map(lambda i: i[0], cur.fetchall()))
    
        # 表格内容顺序，unit编号，成材率，人均吨钢，吨电耗，单位产量，吨备件，产量
        table = []
        for i in crew:
            cur.execute(f'''
                select unit, 
                COALESCE(round(sum(product)/nullif(sum(material),0::numeric), 2), 0),
                COALESCE(round((sum(day_product)/nullif((select sum(staff) from parse_down where day_product != 0 and {hxy_r} and unit = '{i}'), 0::numeric)), 2), 0), 
                coalesce(ROUND(sum(power)/nullif(sum(day_product),0::numeric),2), 0),
                coalesce(ROUND(sum(day_product)/nullif(sum(day),0::numeric),2), 0),
                coalesce(ROUND(sum(part)/nullif(sum(day_product), 0::numeric), 2), 0),
                coalesce(sum(day_product), 0)
                from parse_down
                where {hxy_r} and unit = '{i}'
                group by unit
            ''')
            temp = cur.fetchall()
            table.append(temp[0])
    
        # 返回总计数据
        # unit成材率,人均吨钢,吨电耗,单位产量,吨备件
        cur.execute(f'''
            select unit, 
            COALESCE(round(sum(product)/nullif(sum(material),0::numeric), 2), 0),
            COALESCE(round((sum(day_product)/nullif((select sum(staff) from parse_down where day_product != 0 and {hxy_r} and unit = '{i}'), 0::numeric)), 2), 0), 
            coalesce(ROUND(sum(power)/nullif(sum(day_product),0::numeric),2), 0),
            coalesce(ROUND(sum(day_product)/nullif(sum(day),0::numeric),2), 0),
            coalesce(ROUND(sum(part)/nullif(sum(day_product), 0::numeric), 2), 0),
            coalesce(sum(day_product), 0)
            from parse_down                                       
            where {hxy_r}
            group by unit
        ''')
        temp = cur.fetchall()
        table.append(['总计']+list(temp[0]))

        # 表格计算结束

        # 返回date横坐标数组
        cur.execute(f'''
            select distinct to_char(date, 'YYYY-MM-DD') from parse1 where {hxy_r}
        ''')
        time = list(map(lambda i: i[0], cur.fetchall()))
    
        # 返回unit数据
        cur.execute(f'''
            select distinct unit from parse1 where {hxy_r}
        ''')
        crew = list(map(lambda i: i[0], cur.fetchall()))
        # unit个数，在echarts中有用
        crew_length = len(crew)
    
        # ['product', '127unit', '114unit', '128unit'],
        # ['2021-08-01', 43.3, 85.8, 93.7],
        # ['2021-08-02', 83.1, 73.4, 55.1],
    
        # 图表内容顺序 人均吨钢，吨电耗，单位产量 吨备件和成材率不显示趋势，直接看最上面的总量即可
        # 图表的title文字,同时也可用于搜索
        pic_name = ['production.public.parse1.steel_per_person', 'production.public.parse1.power_per_steel', 'production.public.parse1.success_rate', 'production.public.parse1.steel_per_day']
    
        # 构造dataset数据存储库,分别是cmy[0]到cmy[3]
        cmy = [[['product']], [['product']], [['product']], [['product']]]
        for y in cmy:
            for m in time:
                y.append([m])
        num = 0
        for i in pic_name:
            for j in crew:
                cur.execute(f'''
                        select COALESCE({i}, 0)
                        from parse1
                        where {hxy_r} and unit = '{j}'
                    ''')
                temp = list(map(lambda i: float(i[0]), cur.fetchall()))
                print(temp)
                cmy[num][0].append(j)
                for k in range(len(temp)):
                    cmy[num][k + 1].append(temp[k])
            num = num + 1

    return table, pic_name, cmy, crew_length


def initial_time():
    a = Parse.objects.values_list('date').latest('date')[0].strftime('%Y-%m-%d')
    return a, a